using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Common;
using System.Data;
using System.IO;
using Yogesh.ExcelXml;

//TODO: Add another sheet with help for translators

/// <summary>
/// Exports selected forms and languages to an Excel XML spreadsheet
/// </summary>
public partial class Excel : System.Web.UI.Page
{
    private Database db;
    /// <summary>
    /// Generates, then sends an XML spreadsheet to the browser based on the inputs on the previous page
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void Page_Load(object sender, EventArgs e)
    {
        if(Request.Form.Count == 0) {
            Response.Redirect("~/Default.aspx");
        }
        try {
            ContentPlaceHolder previous = PreviousPage.Master.FindControl("cntMain") as ContentPlaceHolder;
            string applicationId = null;
            if(previous != null) {
                RadioButtonList radApplicationId = (previous.FindControl("radApplication") as RadioButtonList);
                if(radApplicationId != null && radApplicationId.SelectedValue.Trim().Length > 0) {
                    applicationId = radApplicationId.SelectedValue;
                } else {
                    throw new ArgumentNullException("Application not specified");
                }
            }
            // TODO: 3 - add styles to generated sheet
            db = new Database();
            db.OpenConnection();
            string applicationName = null,
                   defaultLanguage = null;

            ExcelXmlWorkbook wb = new ExcelXmlWorkbook();
            using(Database.Command cmd = db.CreateCommand()) {
                cmd.CommandText = "SELECT displayname, defaultLanguage FROM applications WHERE id = @p";
                cmd.AddParameterWithValue("@p", applicationId);
                using(DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleResult)) {
                    while(reader.Read()) {
                        applicationName = reader.GetString(0);
                        defaultLanguage = reader.GetString(1);
                    }
                }
                if(applicationName == null || applicationName.Length == 0) {
                    throw new ArgumentNullException("Application not found");
                }
                List<string> languages = new List<string>();
                languages.Add(defaultLanguage);

                ListBox lstLanguages = (ListBox)previous.FindControl("lstLanguages"),
                        lstBundles = (ListBox)previous.FindControl("lstBundles");

                int startingColumn = 1,
                rowIndex = 1;
                Worksheet sheet = wb[0];
                sheet.Name = "Languages";
                Row row = sheet.AddRow();
                int column = startingColumn + 1;
                //sheet.Table.Columns[defaultHeaderCell.Index].Width = 200;
                XmlStyle greenBackground = new XmlStyle(),
                         greyBackground = new XmlStyle();
                greenBackground.Interior.Color = System.Drawing.Color.FromArgb(234, 241, 221);
                greenBackground.Interior.Pattern = Pattern.Solid;
                greyBackground.Interior.Color = System.Drawing.Color.FromArgb(240, 240, 240);
                greyBackground.Interior.Pattern = Pattern.Solid;
                greyBackground.Font.Color = System.Drawing.Color.FromArgb(130, 130, 130);
                sheet[startingColumn, 0].Value = "Translation ID";
                Cell headerCell = sheet[column++, 0];
                headerCell.Value = defaultLanguage;
                foreach(ListItem itm in lstLanguages.Items) {
                    if(itm.Selected && itm.Value.Length > 0 && itm.Value != defaultLanguage) {
                        headerCell = sheet[column++, 0];
                        headerCell.Value = itm.Value;
                        languages.Add(itm.Value);
                    }
                }
                sheet.FreezeTopRows = 1;
                if(languages.Count == 0) {
                    throw new ArgumentException("No languages specified");
                }

                List<string> forms = new List<string>();
                foreach(ListItem itm in lstBundles.Items) {
                    if(itm.Selected && itm.Value.Length > 0) {
                        forms.Add(itm.Value);
                    }
                }

                //sheet.Table.Columns[startingColumn].Width = 200;
                if(forms.Count > 0) {
                    cmd.CommandText = "SELECT DISTINCT elements.translationid, translations.language, translations.translation, translationcomments.comment FROM elements INNER JOIN translations ON translations.translationid = elements.translationid LEFT JOIN translationcomments ON translationcomments.translationid = translations.translationid WHERE applicationid = @p AND language IN('" + Utils.Implode(languages, "','", false) + "') AND bundlename IN('" + Utils.Implode(forms, "','", false) + "') ORDER BY elements.id";
                } else {
                    cmd.CommandText = "SELECT DISTINCT elements.translationid, translations.language, translations.translation, translationcomments.comment FROM elements INNER JOIN translations ON translations.translationid = elements.translationid LEFT JOIN translationcomments ON translationcomments.translationid = translations.translationid WHERE applicationid = @p AND language IN('" + Utils.Implode(languages, "','", false) + "') ORDER BY elements.id";
                }
                using(DbDataReader reader = cmd.ExecuteReader()) {
                    string lastIdRead = null;
                    while(reader.Read()) {
                        Cell cell = null;
                        if(lastIdRead == null) {
                            lastIdRead = reader["translationid"].ToString();
                            cell = sheet[startingColumn, rowIndex];
                            cell.Value = int.Parse(lastIdRead);
                            cell.Comment = reader["comment"].ToString();
                            cell.Style = greyBackground;
                        } else if(lastIdRead != reader["translationid"].ToString()) {
                            lastIdRead = reader["translationid"].ToString();
                            cell = sheet[startingColumn, ++rowIndex];
                            cell.Value = int.Parse(lastIdRead);
                            cell.Comment = reader["comment"].ToString();
                            cell.Style = greyBackground;
                        }
                        cell = sheet[languages.IndexOf(reader["language"].ToString()) + startingColumn + 1, rowIndex];
                        cell.Value = HttpUtility.HtmlEncode(reader["translation"].ToString());
                    }
                }
                Range defaultColumn = new Range(sheet[startingColumn + 1, 0], sheet[startingColumn + 1, rowIndex]);
                defaultColumn.Style = greenBackground;
                Range headerCells = new Range(sheet[startingColumn, 0], sheet[column, 0]);
                headerCells.Font.Bold = true;
                wb.Properties.Title = "Translations for " + applicationName;
            }
            using(MemoryStream ms  = new MemoryStream()) {
                wb.Export(ms);
                Response.Clear();
                Response.AddHeader("Content-Type", "binary/octet-stream");
                Response.AddHeader("Content-Length", ms.Length.ToString());
                Response.AddHeader("Content-Disposition", "attachment; filename=\"" + applicationName + ".xml\"; size=" + ms.Length.ToString()); // todo: include date in filename
                ms.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }
        } catch(Exception ex) {
            lblError.Text = ex.Message + ex.StackTrace;
        } finally {
            if(db != null) {
                db.CloseConnection();
            }
        }
    }
}
